import json
import logging
import mysql.connector
import schedule
import time
from datetime import datetime
from pathlib import Path
import yaml
from typing import Dict, List, Optional
import sys
import random

# 配置类
class Config:
    def __init__(self, config_path: str = "config.yaml"):
        with open(config_path, 'r', encoding='utf-8') as f:
            self.config = yaml.safe_load(f)
        
        self.source_db = self.config['source_database']
        self.target_db = self.config['target_database']
        self.sync_time_range = self.config['sync_time_range']
        self.sync_interval = self.config['sync_interval']
        self.batch_size = self.config['batch_size']
        self.table_prefix = "jingvin_"
        self.table_count = 10

# 数据库管理类
class DatabaseManager:
    def __init__(self, db_config: Dict):
        self.config = db_config
        # 添加连接超时设置
        if 'connect_timeout' not in self.config:
            self.config['connect_timeout'] = 60  # 默认60秒
        if 'connection_timeout' not in self.config:
            self.config['connection_timeout'] = 60  # 默认60秒
        self.conn = None
        self.cursor = None

    def connect(self):
        try:
            self.conn = mysql.connector.connect(**self.config)
            self.cursor = self.conn.cursor(dictionary=True)
        except Exception as e:
            logging.error(f"数据库连接失败: {str(e)}")
            raise

    def close(self):
        if self.cursor:
            self.cursor.close()
        if self.conn:
            self.conn.close()

    def is_connected(self):
        try:
            return self.conn and self.conn.is_connected()
        except:
            return False

    def ensure_connection(self):
        if not self.is_connected():
            self.close()  # 确保旧连接被关闭
            self.connect()

# 同步管理器
class SyncManager:
    def __init__(self, config: Config):
        self.config = config
        self.source_db = DatabaseManager(config.source_db)
        self.target_db = DatabaseManager(config.target_db)
        self.sync_state_file = "sync_state.json"
        self.sync_state = self.load_sync_state()
        self.setup_logging()

    def setup_logging(self):
        logging.basicConfig(
            level=logging.INFO,
            format='%(asctime)s - %(levelname)s - %(message)s',
            handlers=[
                logging.FileHandler('sync.log'),
                logging.StreamHandler(sys.stdout)
            ]
        )

    def load_sync_state(self) -> Dict:
        try:
            if Path(self.sync_state_file).exists():
                with open(self.sync_state_file, 'r') as f:
                    return json.load(f)
            return {"tables": {}}
        except Exception as e:
            logging.error(f"加载同步状态失败: {str(e)}")
            return {"tables": {}}

    def save_sync_state(self):
        try:
            with open(self.sync_state_file, 'w') as f:
                json.dump(self.sync_state, f)
        except Exception as e:
            logging.error(f"保存同步状态失败: {str(e)}")

    def filter_and_transform_data(self, data: Dict) -> Optional[Dict]:
        try:
            api_result = json.loads(data['ApiResault'])
            if api_result.get('code') != '000000':
                return None
            
            # 转换并压缩result字段
            result_str = json.dumps(api_result['result'], ensure_ascii=False, separators=(',', ':'))
            data['ApiResault'] = result_str
            return data
        except Exception:
            return None

    def is_within_sync_time_range(self) -> bool:
        current_hour = datetime.now().hour
        start_hour = self.config.sync_time_range['start']
        end_hour = self.config.sync_time_range['end']
        
        if start_hour <= end_hour:
            return start_hour <= current_hour <= end_hour
        else:  # 跨越午夜的情况
            return current_hour >= start_hour or current_hour <= end_hour

    def sync_table(self, table_name: str):
        max_retries = 3
        retry_count = 0
        
        while retry_count < max_retries:
            try:
                self.source_db.connect()
                self.target_db.connect()

                # 检查目标表是否存在
                self.target_db.cursor.execute(f"SHOW TABLES LIKE '{table_name}'")
                table_exists = self.target_db.cursor.fetchone() is not None

                if not table_exists:
                    # 创建表（不包含索引）
                    create_table_sql = f"""
                    CREATE TABLE `{table_name}` (
                        `Id` int NOT NULL AUTO_INCREMENT,
                        `VinCode` varchar(40) DEFAULT NULL,
                        `VinDecodeType` int DEFAULT NULL,
                        `ApiResault` text,
                        `LastEditTime` datetime(3) DEFAULT NULL,
                        `oid` int DEFAULT NULL,
                        `createTime` datetime(3) DEFAULT NULL,
                        PRIMARY KEY (`Id`),
                        UNIQUE KEY `IX_JingVIN_0_VinCode` (`VinCode`)
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
                    """
                    self.target_db.cursor.execute(create_table_sql)
                    last_id = 0
                else:
                    # 获取最大ID
                    self.target_db.cursor.execute(f"SELECT MAX(Id) as max_id FROM {table_name}")
                    result = self.target_db.cursor.fetchone()
                    last_id = result['max_id'] if result['max_id'] else 0

                logging.info(f"开始同步表 {table_name}，last_id={last_id}")

                # 分批同步数据
                while True:
                    # 检查是否在同步时间范围内
                    if not self.is_within_sync_time_range():
                        logging.info(f"表 {table_name} 同步暂停：当前时间不在同步时间范围内，等待5分钟")
                        time.sleep(60 * 5)  # 等待30分钟
                        continue
                    try:
                        # 检查连接状态
                        if not self.source_db.conn.is_connected():
                            self.source_db.connect()
                        if not self.target_db.conn.is_connected():
                            self.target_db.connect()

                        self.source_db.cursor.execute(
                            f"SELECT * FROM {table_name} WHERE Id > %s ORDER BY Id LIMIT %s",
                            (last_id, self.config.batch_size)
                        )
                        rows = self.source_db.cursor.fetchall()
                        
                        if not rows:
                            break

                        filtered_rows = []
                        for row in rows:
                            filtered_row = self.filter_and_transform_data(row)
                            if filtered_row:
                                filtered_rows.append(filtered_row)

                        if filtered_rows:
                            # 检查目标数据库连接
                            if not self.target_db.conn.is_connected():
                                self.target_db.connect()

                            # 构建插入SQL
                            columns = filtered_rows[0].keys()
                            values = [tuple(row[column] for column in columns) for row in filtered_rows]
                            
                            insert_sql = f"""
                                INSERT INTO {table_name} 
                                ({','.join(columns)}) 
                                VALUES ({','.join(['%s'] * len(columns))})
                                ON DUPLICATE KEY UPDATE
                                    VinDecodeType = VALUES(VinDecodeType),
                                    ApiResault = VALUES(ApiResault),
                                    LastEditTime = VALUES(LastEditTime),
                                    oid = VALUES(oid),
                                    createTime = VALUES(createTime)
                            """
                        
                            self.target_db.cursor.executemany(insert_sql, values)
                            self.target_db.conn.commit()

                        last_id = rows[-1]['Id']
                        self.sync_state['tables'][table_name] = {'last_id': last_id, 'last_sync': datetime.now().isoformat()}
                        self.save_sync_state()
                        logging.info(f"表 {table_name}：已同步 {len(filtered_rows)} 条数据，last_id = {last_id}")
                        
                        # 随机等待1-3秒
                        time.sleep(random.uniform(1, 3))

                    except mysql.connector.Error as e:
                        if e.errno in (2013, 2055):  # Lost connection or cursor not connected
                            logging.warning(f"数据库连接丢失，准备重新连接: {str(e)}")
                            self.source_db.close()
                            self.target_db.close()
                            time.sleep(5)  # 短暂等待后重试
                            continue
                        raise

                # 同步成功，跳出重试循环
                break

            except Exception as e:
                retry_count += 1
                if retry_count < max_retries:
                    logging.error(f"发生错误，等待30秒后进行第{retry_count}次重试: {str(e)}")
                    time.sleep(30)
                    continue
                logging.error(f"同步表 {table_name} 失败: {str(e)}")
                raise
            finally:
                self.source_db.close()
                self.target_db.close()

    def update_table(self, table_name: str):
        max_retries = 3
        retry_count = 0
        
        while retry_count < max_retries:
            try:
                self.source_db.connect()
                self.target_db.connect()

                last_sync_time = self.sync_state['tables'].get(table_name, {}).get('last_sync')
                if not last_sync_time:
                    logging.warning(f"表 {table_name} 没有上次同步记录，跳过更新")
                    return
                
                logging.info(f"开始更新表 {table_name}")

                while True:
                    # 检查是否在同步时间范围内
                    if not self.is_within_sync_time_range():
                        logging.info(f"表 {table_name} 同步暂停：当前时间不在同步时间范围内，等待5分钟")
                        time.sleep(60 * 5)  # 等待30分钟
                        continue

                    try:
                        # 检查连接是否有效，如果无效则重新连接
                        if not self.source_db.conn.is_connected():
                            self.source_db.connect()
                        if not self.target_db.conn.is_connected():
                            self.target_db.connect()

                        self.source_db.cursor.execute(
                            f"SELECT * FROM {table_name} WHERE LastEditTime > %s ORDER BY LastEditTime LIMIT %s",
                            (last_sync_time, self.config.batch_size)
                        )
                        rows = self.source_db.cursor.fetchall()

                        if not rows:
                            break

                        filtered_rows = []
                        for row in rows:
                            filtered_row = self.filter_and_transform_data(row)
                            if filtered_row:
                                filtered_rows.append(filtered_row)

                        if filtered_rows:
                            for row in filtered_rows:
                                # 检查目标数据库连接
                                if not self.target_db.conn.is_connected():
                                    self.target_db.connect()
                                    
                                update_sql = f"""
                                UPDATE {table_name} SET 
                                    VinDecodeType = %s,
                                    ApiResault = %s,
                                    LastEditTime = %s,
                                    oid = %s,
                                    createTime = %s
                                WHERE VinCode = %s
                                """
                                self.target_db.cursor.execute(update_sql, (
                                    row['VinDecodeType'],
                                    row['ApiResault'],
                                    row['LastEditTime'],
                                    row['oid'],
                                    row['createTime'],
                                    row['VinCode']
                                ))
                            self.target_db.conn.commit()

                        last_sync_time = rows[-1]['LastEditTime']
                        self.sync_state['tables'][table_name]['last_sync'] = last_sync_time.isoformat()
                        self.save_sync_state()
                        logging.info(f"表 {table_name}：已更新 {len(filtered_rows)} 条数据，LastEditTime = {last_sync_time}")

                        # 随机等待1-3秒
                        time.sleep(random.uniform(1, 3))

                    except mysql.connector.Error as e:
                        if e.errno in (2013, 2055):  # Lost connection or cursor not connected
                            logging.warning(f"数据库连接丢失，准备重新连接: {str(e)}")
                            self.source_db.close()
                            self.target_db.close()
                            time.sleep(5)  # 短暂等待后重试
                            continue
                        raise

                # 更新成功，跳出重试循环
                break

            except Exception as e:
                retry_count += 1
                if retry_count < max_retries:
                    logging.error(f"发生错误，等待30秒后进行第{retry_count}次重试: {str(e)}")
                    time.sleep(30)
                    continue
                logging.error(f"更新表 {table_name} 失败: {str(e)}")
                raise
            finally:
                self.source_db.close()
                self.target_db.close()

def main():
    config = Config()
    sync_manager = SyncManager(config)

    def sync_job():
        for i in range(config.table_count):
            table_name = f"{config.table_prefix}{i}"
            try:
                sync_manager.sync_table(table_name)
                sync_manager.update_table(table_name)
            except Exception as e:
                logging.error(f"处理表 {table_name} 时发生错误: {str(e)}")

    # 设置定时任务
    schedule.every(config.sync_interval).minutes.do(sync_job)

    # 立即执行一次
    sync_job()

    # 持续运行定时任务
    while True:
        schedule.run_pending()
        time.sleep(60)

if __name__ == "__main__":
    main() 